﻿using System;
using System.Collections.Generic;
using System.Text;
using Hanhud.Pattern;
using Hanhud.Web.Publish;
using System.Data;
using Hanhud.Data;
using System.Data.SqlClient;

namespace Hanhud.Web.Data.Providers
{
    internal class CPublishProvider
    {
        private CDbAccess _db = new CDbAccess();
 
        public static CPublishProvider Instance
        {
            get
            {
                return CPageSingleton<CPublishProvider>.Instance;
            }
        }

        internal string GetArticleBody(string articleID, bool isPlainBody)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Articles_GetArticleBody"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ArticleID", SqlDbType.VarChar, 50).Value = articleID;
            cmd.Parameters.Add("@IsPlainBody", SqlDbType.Bit).Value = isPlainBody;

            object retv = _db.ExecuteSalar(cmd);
            return retv.ToString();
        }

        internal DataRow GetArticle(string articleID)
        {
            DataTable tblArticle = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Articles_GetArticle"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ArticleID", SqlDbType.VarChar, 50).Value = articleID;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                tblArticle.Load(_db.ExecuteReader(cmd, CommandBehavior.SingleRow), LoadOption.Upsert);
            }

            return tblArticle.Rows.Count > 0 ? tblArticle.Rows[0] : null;
        }

        internal DataTable GetArticles(int pageIndex, int pageSize, out int resultCount)
        {
            DataTable tblArticle = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Articles_GetArticles"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
            cmd.Parameters.Add("@ResultCount", SqlDbType.Int).Direction = ParameterDirection.Output;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                tblArticle.Load(_db.ExecuteReader(cmd, CommandBehavior.SingleRow), LoadOption.Upsert);
            }

            resultCount = (int)cmd.Parameters["@ResultCount"].Value;

            return tblArticle;
        }

        internal DataTable GetArticles(int categoryID, int pageIndex, int pageSize, out int resultCount)
        {
            DataTable tblArticle = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Articles_GetArticles"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ArticleCategoryID", SqlDbType.Int).Value = categoryID;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
            cmd.Parameters.Add("@ResultCount", SqlDbType.Int).Direction = ParameterDirection.Output;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                tblArticle.Load(_db.ExecuteReader(cmd, CommandBehavior.SingleRow), LoadOption.Upsert);
            }

            resultCount = (int)cmd.Parameters["@ResultCount"].Value;

            return tblArticle;
        }

        internal DataTable GetPublishedArticles(DateTime dateTime, int pageIndex, int pageSize, out int resultCount)
        {
            DataTable tblArticle = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Articles_GetPublishedArticles"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@CurrentDate", SqlDbType.DateTime).Value = dateTime;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
            cmd.Parameters.Add("@ResultCount", SqlDbType.Int).Direction = ParameterDirection.Output;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                tblArticle.Load(_db.ExecuteReader(cmd, CommandBehavior.SingleResult), LoadOption.Upsert);
            }

            resultCount = (int)cmd.Parameters["@ResultCount"].Value;

            return tblArticle;
        }

        internal DataTable GetPublishedArticles(int categoryId, DateTime dateTime, int pageIndex, int pageSize)
        {
            DataTable tblArticle = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Articles_GetPublishedArticlesByCategory"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryId;
            cmd.Parameters.Add("@CurrentDate", SqlDbType.DateTime).Value = dateTime;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                tblArticle.Load(_db.ExecuteReader(cmd, CommandBehavior.SingleResult), LoadOption.Upsert);
            }

            return tblArticle;
        }

        internal DataTable GetRelatedPublishedArticles(string articleID, DateTime dateTime, int categoryID, int count)
        {
            DataTable tblArticle = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Articles_GetRelatedPublishedArticles"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ArticleID", SqlDbType.VarChar, 50).Value = articleID;
            cmd.Parameters.Add("@CurrentDate", SqlDbType.DateTime).Value = dateTime.ToString(Config.CSiteConfig.Current.ShortDbDateTimeFormat);
            cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
            cmd.Parameters.Add("@ResultCount", SqlDbType.Int).Value = count;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                tblArticle.Load(_db.ExecuteReader(cmd, CommandBehavior.Default), LoadOption.Upsert);
            }

            return tblArticle;
        }

        internal bool ApproveArticle(string articleID)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Articles_ApproveArticle"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ArticleID", SqlDbType.VarChar, 50).Value = articleID;

            int retv = _db.ExecuteNonQuery(cmd);

            return retv > 0;
        }

        internal bool RateArticle(string articleID, int rating)
        {
            throw new NotImplementedException();
        }

        internal bool IncrementArticleViewCount(string articleID)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Articles_IncrementArticleViewCount"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ArticleID", SqlDbType.VarChar, 50).Value = articleID;

            int retv = _db.ExecuteNonQuery(cmd);

            return retv > 0;
        }

        internal int GetArticleCount()
        {
            throw new NotImplementedException();
        }

        internal int GetArticleCount(int categoryId)
        {
            throw new NotImplementedException();
        }

        internal int GetPublishedArticleCount(DateTime dateTime)
        {
            throw new NotImplementedException();
        }

        internal int GetPublishedArticleCount(int categoryId, DateTime dateTime)
        {
            throw new NotImplementedException();
        }

        internal bool DeleteArticle(string articleID)
        {
            throw new NotImplementedException();
        }

        internal string InsertArticle(string articleID, DateTime addedDate, string userName, int articleCategoryID, string title, string imageUrl, string aastract, string body, DateTime releaseDate, DateTime expireDate, bool approved, bool listed, bool commentsEnabled, bool onlyForMembers)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Articles_InsertArticle"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ArticleID", SqlDbType.VarChar, 50).Value = articleID;
            cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = addedDate.ToString(Config.CSiteConfig.Current.FullDbDateTimeFormat);
            cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = userName;
            cmd.Parameters.Add("@ArticleCategoryID", SqlDbType.Int).Value = articleCategoryID;
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 500).Value = title;
            cmd.Parameters.Add("@ImageUrl", SqlDbType.NVarChar, 500).Value = imageUrl;
            cmd.Parameters.Add("@Abstract", SqlDbType.NVarChar, 2000).Value = aastract;
            cmd.Parameters.Add("@ReleaseDate", SqlDbType.DateTime).Value = releaseDate.ToString(Config.CSiteConfig.Current.ShortDbDateTimeFormat);
            cmd.Parameters.Add("@ExpireDate", SqlDbType.DateTime).Value = expireDate.ToString(Config.CSiteConfig.Current.ShortDbDateTimeFormat);
            cmd.Parameters.Add("@Approved", SqlDbType.Bit).Value = approved;
            cmd.Parameters.Add("@Listed", SqlDbType.Bit).Value = listed;
            cmd.Parameters.Add("@CommentsEnabled", SqlDbType.Bit).Value = commentsEnabled;
            cmd.Parameters.Add("@OnlyForMembers", SqlDbType.Bit).Value = onlyForMembers;

            int retv = _db.ExecuteNonQuery(cmd);
            return retv > 0 ? articleID : string.Empty;
        }

        internal List<CArticleCategory> GetAllCategories()
        {
            List<CArticleCategory> categories = new List<CArticleCategory>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ArticleCategories_GetAllCategories"));
            cmd.CommandType = CommandType.StoredProcedure;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                while (reader.Read())
                {
                    categories.Add(GetArticleCategoryFromReader(reader));
                }
            }

            return categories;
        }

        internal List<CArticleCategory> GetCategoryInfo(int articleCategoryID)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ArticleCategories_GetCategoryInfo"));
            cmd.CommandType = CommandType.StoredProcedure;

            List<CArticleCategory> categories = new List<CArticleCategory>();
            cmd.Parameters.Add("@ArticleCategoryID", SqlDbType.Int).Value = articleCategoryID;
            using (CDbConnectionManager m = _db.GetConnectionManager())
            {

                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                while (reader.Read())
                {
                    categories.Add(GetArticleCategoryFromReader(reader));
                }
            }

            return categories;
        }

        private CArticleCategory GetArticleCategoryFromReader(IDataReader reader)
        {
            return
                new CArticleCategory(
                        (int)reader["ArticleCategoryID"],
                        (int)reader["ParentArticleCategoryID"],
                        reader["Title"].ToString(),
                        reader["Description"].ToString(),
                        (bool)reader["IsPrivate"]
                    );
        }
    }
}
